package net.lunjin.dao.impl;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;

import net.lunjin.bean.News;
import net.lunjin.dao.NewsDao;
import finaler.util.Page;
import finaler.util.PageList;
import finaler.util.SQLUtil;

public class NewsDaoImpl extends MiddleAbstractDao<News> implements NewsDao {
	
	@Override
	protected Class<News> getEntityClass() {
		return News.class;
	}

	@Override
	public PageList getNewsList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		whereSql.append(" where (newsType = " + page.getParameter("newsType")+")" );
		if(page.getParameter("searchkey")!=null && page.getParameter("searchkey").equals("")==false ){
			whereSql.append(" and newstitle like '%"+page.getParameter("searchkey")+"%'");
		}
		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from news ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select *");
		queryListSql.append(" from news  ");
		queryListSql.append( whereSql );
		queryListSql.append( " order by ");
		queryListSql.append( page.getParameter("sort")==null?"id desc":page.getParameter("sort") );//无sort默认id排
		SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		pageSQL.setFirstResult( page.getStart() );//固定
		pageSQL.setMaxResults( page.getRange() );//固定
		List<News> dataList = super.findBySqlToBeanList(pageSQL.getSql());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public List<News> getRandomRecord(int newsType,int limit) throws Exception {
		String sql = " select * from "+tableName()+" where newstype="+newsType+ " ORDER BY RAND() LIMIT  " + limit;
		return super.findBySqlToBeanList(sql);
	}

	@Override
	public List<News> getTopRecord(int newsType,int limit) throws Exception {
		String sql = " select * from "+tableName()+" where newstype="+newsType+ " order by id desc LIMIT  " + limit;
		return super.findBySqlToBeanList(sql);
	}

	@Override
	public List<News> getHotRecord(int newsType, int limit) throws Exception {
		String sql = " select * from "+tableName()+" where newstype="+newsType+ " order by hit desc LIMIT  " + limit;
		return super.findBySqlToBeanList(sql);
	}
	
	@Override
	public List<News> getBbsTopRecord(int limit) throws Exception {
		String sql = " select * from "+tableName()+" where newstype >2 and newstype < 12 order by id desc LIMIT  " + limit;
		return super.findBySqlToBeanList(sql);
	}

	@Override
	public PageList getBbsList(Page page) throws Exception {
		StringBuffer whereSql = new StringBuffer();
		if(page.getParameter("bbsType")!=null && page.getParameter("bbsType").equals("")==false ){
			whereSql.append(" where newsType = " + page.getParameter("bbsType") );
		}else{
			whereSql.append(" where newstype >2 and newstype < 12 ");
		}
		if(page.getParameter("searchkey")!=null && page.getParameter("searchkey").equals("")==false ){
			whereSql.append(" and newstitle = "+page.getParameter("searchkey"));
		}
		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from news ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select a.id,a.newstitle,a.hit,a.createtime,a.newstype,b.rmcount, ");
		queryListSql.append(" ( case when b.lasttime   is null then a.createtime else b.lasttime end) as lasttime ");
		queryListSql.append(" from news a ");
		queryListSql.append(" left join (select newsid,count(newsid) as rmcount,max(createtime) as lasttime  ");
		queryListSql.append(" from newsremark  group by  newsid) b  on b.newsid = a.id ");
		queryListSql.append( whereSql );
		queryListSql.append("  order by lasttime desc" );
		SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		pageSQL.setFirstResult( page.getStart() );//固定
		pageSQL.setMaxResults( page.getRange() );//固定
		List<Map<String, Object>> dataList = super.findBySqlToMapList(pageSQL.getSql());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public List<News> getBeforeAfterList(BigDecimal newsId, BigDecimal newsType) throws Exception {
		String sql = "(SELECT id,newstitle FROM news p WHERE p.id<"+newsId+" and p.newstype = "+newsType + " order by id desc limit 1 )";
			sql += " UNION ";
			sql += " (SELECT id,newstitle FROM news n WHERE n.id>"+newsId+" and n.newstype =  "+newsType + " order by id asc limit 1)"; 
		return super.findBySqlToBeanList(sql);
	}

	@Override
	public void autoUpdateHit() throws Exception {
		String sqlString = "update news set hit =  hit+1";
		super.executeSql(sqlString);
	}

	@Override
	public News getRandomBBSRecord() throws Exception {
		StringBuffer querySql = new StringBuffer();
		querySql.append(" select a.*  from news a left join (select newsid,count(id) as rmcount from newsremark ) b on b.newsid = a.id");
		querySql.append(" where newstype >2 and newstype < 12 and ( rmcount is null or rmcount<2 ) ORDER BY RAND() LIMIT 1 ");
		return 	super.findBySqlToBean(querySql.toString());
	}

	@Override
	public News getByBaiduId(String baiduid) throws Exception {
		String sqlString = "select id from "+tableName()+" where baiduid='"+baiduid+"'";
		return 	super.findBySqlToBean(sqlString);
	}

	@Override
	public PageList getAll(Page page) throws Exception {

		StringBuffer whereSql = new StringBuffer();
		PageList pageList = new PageList(page);//固定
		StringBuffer sqlCount = new StringBuffer();
		sqlCount.append("select count(1) from news ");
		sqlCount.append( whereSql );
		int dataCount = super.getIntBySql( sqlCount.toString() );//取总
		pageList.setTotal(dataCount);//总数
		
		StringBuffer queryListSql = new StringBuffer();
		queryListSql.append(" select *");
		queryListSql.append(" from news  ");
		queryListSql.append( whereSql );
		queryListSql.append( " order by id desc" );
		SQLUtil pageSQL = new SQLUtil(queryListSql);//固定
		pageSQL.setFirstResult( page.getStart() );//固定
		pageSQL.setMaxResults( page.getRange() );//固定
		List<News> dataList = super.findBySqlToBeanList(pageSQL.getSql());//数据集
		pageList.setList(dataList);
		return pageList;
	}

	@Override
	public News findByTitle(String title,int newsType) throws Exception {
		String sqlString = "select id from "+tableName()+" where newstitle='"+title+"' and newstype = "+newsType;
		return 	super.findBySqlToBean(sqlString);
	}

	
}
